<?php
	// 输出Excel文件头，可把user.csv换成你要的文件名 
	$GX  ="'".implode("','",$_POST["Fruit"])."'";
	header('Content-Type: application/vnd.ms-excel'); 
	header('Content-Disposition: attachment;filename="user.csv"'); 
	header('Cache-Control: max-age=0'); 
	
	//echo iconv('utf-8', 'gbk',implode(',',$GX));
	
	// 从数据库中获取数据，为了节省内存，不要把数据一次性读到内存，从句柄中一行一行读即可 
	$conn=mysql_connect("127.0.0.1:3306","root","tdled2018");
	if (!$conn){
		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db("tddb", $conn);
	
	mysql_query("set character set 'utf8'");//读库 
	
	mysql_query("set names 'utf8'");//写库 
	
	$SQL="
	SELECT 
		 `Yi`.`GongDanHao`
		, `Yi`.`GongXu`
		, `Yi`.`sjQty`
		, `keepwo`.`TouChanQty`
		, (`keepwo`.`TouChanQty` - `Yi`.`sjQty`) AS `wipQty`
	FROM 
		(
		SELECT 
			 `GongDanHao` 
			, `d_h_temp`.`GongXu`
			, sum( `ShiJiQTY` ) AS `sjQty`
		FROM 
			 `d_d_temp` 
			, `d_h_temp`
		WHERE 
			`d_d_temp`.`HID` = `d_h_temp`.`HID`
		GROUP BY 
			 `d_d_temp`.`GongDanHao` 
			, `d_h_temp`.`GongXu`
		) AS `Yi`
		, `keepwo`
	WHERE 
		`Yi`.`GongDanHao` = `keepwo`.`GongDan`
	AND 
		`Yi`.`GongXu` 
	IN 
		(".
			$GX.
		")"
	;
	//echo iconv('utf-8', 'gbk', $SQL);
	$stmt = mysql_query($SQL);//查詢WIP
	
	// 打开PHP文件句柄，php://output 表示直接输出到浏览器 
	$fp = fopen('php://output', 'a'); 
	
	// 输出Excel列名信息 
	$head = array('工單號','工序', '實際完成數', '投產數', 'WIP'); 
	foreach ($head as $i => $v) {
		// CSV的Excel支持GBK编码，一定要转换，否则乱码 
		$head[$i] = iconv('utf-8', 'gbk', $v);
	}
	
	// 将数据通过fputcsv写到文件句柄 
	fputcsv($fp, $head);

	// 计数器 
	$cnt = 0;
	// 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小 
	$limit = 100000;
	
	// 逐行取出数据，不浪费内存
	while ($row = mysql_fetch_row($stmt)){
		//echo json_encode($row)."<br/>";
		$cnt ++;
		if ($limit == $cnt) {//刷新一下输出buffer，防止由于数据过多造成问题 
			ob_flush();
			flush();
			$cnt = 0;
		}
		
		foreach ($row as $i => $v) {
			$row[$i] = iconv('utf-8', 'gbk', $v);
		}
		
		fputcsv($fp, $row);
		
		
	}
	fclose($fp); //最后要关闭资源
	
	
	
	
	
	
	
	/*
	//这里开始凑今日完成的语句
	$sql = 
	"SELECT DISTINCTROW 
		`d_h_temp`.`GongXu`,
		`d_h_temp`.`RiQi`,
		`d_d_temp`.`GongDanHao`,
		SUM(`keepwo`.`Qty`) AS `WoQTY`,
		SUM(`d_d_temp`.`ShiJiQTY`) AS `ShiJiQTY`
	FROM
		`d_h_temp`,
		`d_d_temp`,
		`keepwo`
	WHERE
		`d_h_temp`.`HID` = `d_d_temp`.`HID` 
	AND
		`d_d_temp`.`GongDanHao` = `keepwo`.`GongDan`
	AND 
		`d_h_temp`.`RiQi` >= '".$sjj1."'
	AND 
		`d_h_temp`.`RiQi` <= '".$sjj2."'
	GROUP BY
		`d_h_temp`.`GongXu`,
		`d_h_temp`.`RiQi`,
		`d_d_temp`.`GongDanHao`"
	;
	//逐句取出来,然后计算昨日余数和今日接收(昨日余数=PHP日期-1的接收-PHP日期-1的完成)(今日接收=上一工序的今日完成)
	
	
	/*
	
	pdo 抓投產數(工單數量)
	$sql = select 工單 from d_d_temp 機台
	       where $sjj1  $sjj2  符合條件的工單
		   
	兩個元素的陣列，一個放工單號，一個放數量	
    arr[0]=5,arr[1]=15,
		arr['工單號']=投產數;
	foreach 每一工單號
     pdo 抓投產數
		寫入陣列
	
	
	insert into WIP表//2018-3-21 23:39:34注释:已经写在KeepNEW里面了
    字段...................
	//select A, B, C, D, E, F, 從	d_d_temp來
	
	
	
	*/
	
	/*
	SELECT DISTINCTROW 
		`d_h_temp`.`GongXu`,
		`d_h_temp`.`RiQi`,
		`d_d_temp`.`GongDanHao`,
		SUM(`d_d_temp`.`ShiJiQTY`) AS `ShiJiQTY`
	FROM
		`d_h_temp`,
		`d_d_temp`
	WHERE
		`d_h_temp`.`HID` = `d_d_temp`.`HID` 
	AND 
		`d_h_temp`.`RiQi` >= '2018-03-01'
	AND 
		`d_h_temp`.`RiQi` <= '2018-03-31'
	GROUP BY
		`d_h_temp`.`GongXu`,
		`d_h_temp`.`RiQi`,
		`d_d_temp`.`GongDanHao`
	*/
	
	/*
	$sql = 
	"SELECT 
		`d_d_temp`.`HID`, 
		`DID`,
		`GongXu`, 
		`RiQi`, 
		`BanBie`, 
		`ZhiBiao`
		`DingDanHao`, 
		`ChanPinXingHao`, 
		`ChanPinLeiXing`, 
		`ChanPinXiLie`, 
		`GongDanHao`, 
		`JiTai`, 
		`JiHuaQTY`, 
		`JiHuaTime`, 
		`CaoZuoYuan`, 
		`ShiJiQTY`, 
		`ShiJiTime`, 
		`UPH`, 
		`DaiJiTime`, 
		`DianJianTime`, 
		`HuanDanTime`, 
		`HuanHaoCaiTime`, 
		`QingYeJuTime`, 
		`ShouJianTime`, 
		`TiaoJiTime`, 
		`GongChengShiYangiTime`, 
		`GuZhangTime`, 
		`PinZhiYiChangTime`, 
		`DaiLiaoTime`, 
		`ShuiDianQiYiChangiTime`, 
		`QiTaJiHuaWaiTingJiTime`, 
		`WeiDaChengYuanYinBeiZhu`
	FROM 
		`d_h_temp`,
		`d_d_temp` 
	WHERE 
		`d_h_temp`.`HID` = `d_d_temp`.`HID` and `RiQi` between '".$sjj1."' and '".$sjj2."'"
	;
	*/
	/*
	if($GX===""){
		$sql = 
		"SELECT 
			`d_d_temp`.`HID`, 
			`DID`,
			`GongXu`, 
			`RiQi`, 
			`BanBie`, 
			`ZhiBiao`,
			`DingDanHao`, 
			`ChanPinXingHao`, 
			`ChanPinLeiXing`, 
			`ChanPinXiLie`, 
			`GongDanHao`, 
			`JiTai`, 
			`JiHuaQTY`, 
			`JiHuaTime`, 
			`CaoZuoYuan`, 
			`ShiJiQTY`, 
			`ShiJiTime`, 
			`UPH`, 
			`DaiJiTime`, 
			`DianJianTime`, 
			`HuanDanTime`, 
			`HuanHaoCaiTime`, 
			`QingYeJuTime`, 
			`ShouJianTime`, 
			`TiaoJiTime`, 
			`GongChengShiYangiTime`, 
			`GuZhangTime`, 
			`PinZhiYiChangTime`, 
			`DaiLiaoTime`, 
			`ShuiDianQiYiChangiTime`, 
			`QiTaJiHuaWaiTingJiTime`, 
			`WeiDaChengYuanYinBeiZhu`
		FROM 
			`d_h_temp`,
			`d_d_temp` 
		WHERE 
			`d_h_temp`.`HID` = `d_d_temp`.`HID` and `RiQi` between '".$sjj1."' and '".$sjj2."'"
		;
	}else{
		$sql = 
		"SELECT 
			`d_d_temp`.`HID`, 
			`DID`,
			`GongXu`, 
			`RiQi`, 
			`BanBie`, 
			`ZhiBiao`,
			`DingDanHao`, 
			`ChanPinXingHao`, 
			`ChanPinLeiXing`, 
			`ChanPinXiLie`, 
			`GongDanHao`, 
			`JiTai`, 
			`JiHuaQTY`, 
			`JiHuaTime`, 
			`CaoZuoYuan`, 
			`ShiJiQTY`, 
			`ShiJiTime`, 
			`UPH`, 
			`DaiJiTime`, 
			`DianJianTime`, 
			`HuanDanTime`, 
			`HuanHaoCaiTime`, 
			`QingYeJuTime`, 
			`ShouJianTime`, 
			`TiaoJiTime`, 
			`GongChengShiYangiTime`, 
			`GuZhangTime`, 
			`PinZhiYiChangTime`, 
			`DaiLiaoTime`, 
			`ShuiDianQiYiChangiTime`, 
			`QiTaJiHuaWaiTingJiTime`, 
			`WeiDaChengYuanYinBeiZhu`
		FROM 
			`d_h_temp`,
			`d_d_temp` 
		WHERE 
			`d_h_temp`.`HID` = `d_d_temp`.`HID` and `RiQi` between '".$sjj1."' and '".$sjj2."' and `GongXu` IN (".$GX.")"
		;
	}
	//echo $sql."<br/>";
	$stmt = mysql_query($sql); //stmt=查詢日期截
	
	// 打开PHP文件句柄，php://output 表示直接输出到浏览器 
	$fp = fopen('php://output', 'a'); 
	
	// 输出Excel列名信息 
	$head = array('表頭編號','表深編號', '工序', '日期', '班別', '製表人', '訂單號', '產品型號', '產品類型','產品系列', '工單號', '機台號', '排配數量', '計劃工時', '操作員', '實際數量', '實際工時', 'UPH', '待機時間','日點檢保養','工單切換調機','更換耗材','冶具清理','首件','生產調機','工程試樣','設備故障停機','品質異常停機','待料','水電氣異常','其他計劃外停機','未達成原因備註'); 
	foreach ($head as $i => $v) {
		// CSV的Excel支持GBK编码，一定要转换，否则乱码 
		$head[$i] = iconv('utf-8', 'gbk', $v);
	}
	
	// 将数据通过fputcsv写到文件句柄 
	fputcsv($fp, $head);

	// 计数器 
	$cnt = 0;
	// 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小 
	$limit = 100000;
	
	// 逐行取出数据，不浪费内存
	while ($row = mysql_fetch_row($stmt)){
		//echo json_encode($row)."<br/>";
		$cnt ++;
		if ($limit == $cnt) {//刷新一下输出buffer，防止由于数据过多造成问题 
			ob_flush();
			flush();
			$cnt = 0;
		}
		
		foreach ($row as $i => $v) {
			$row[$i] = iconv('utf-8', 'gbk', $v);
		}
		
		fputcsv($fp, $row);
		
		
	}
	fclose($fp); //最后要关闭资源
	*/
?>